package com.starbaba.core.utils;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

/**
 * 分页SQL语句组装类
 * 
 * @author xlj
 * 
 */
public class SQLPageUtils {
	private Log log = LogFactory.getLog(this.getClass());
	private static final String TABLE_ALIAS = "alias";

	private static final String TABLE_ID = "ID";

	private static final String SQL_WITH = "with";

	private static final String SQL_AS = "as";

	private static final String SQL_BRACKET_LEFT = ")";

	private static final String SQL_BRACKET_RIGTH = "(";

	private static final String SQL_ORDER_BY = "order by";

	private static final String SQL_BETWEEN = "between";

	private static final String SQL_AND = "and";

	private static final String SQL_SELECT_FROM = "select * from";

	private static final String SQL_WHERE = "where";

	private static final String SQL_FROM = "from";

	private static final String SQL_SELECT_COUNT_FORM = "select count(*) from ";

	private static SQLPageUtils pageUtil = null;

	private SQLPageUtils() {

	}

	public static SQLPageUtils getInstance() {
		if (pageUtil == null) {
			pageUtil = new SQLPageUtils();
		}
		return pageUtil;
	}

	public String getNewSqlOrderBy(String sql) {
		if (sql != null && sql.indexOf(SQL_ORDER_BY) >= 0) {
			sql = sql.substring(0, sql.indexOf(SQL_ORDER_BY));
		}
		return sql;
	}

	/**
	 * 组装分页SQL语句
	 * 
	 * @param sql
	 * @param indexCount
	 * @param totalCount
	 * @return
	 */
	public String getAssemblyAfterSql(String sql, int indexCount, long totalCount) {
		// SQL WITH语句定义
		String beginSql = SQL_WITH + " " + TABLE_ALIAS + " " + SQL_AS + " "
				+ SQL_BRACKET_RIGTH;
		// SQL 结束语句定义
		String endSql = SQL_BRACKET_LEFT + " " + SQL_SELECT_FROM + " "
				+ TABLE_ALIAS + " " + SQL_WHERE + " " + TABLE_ID + " "
				+ SQL_BETWEEN + " " + indexCount + " " + SQL_AND + " "
				+ totalCount + " " + SQL_ORDER_BY + " " + TABLE_ID;
		// 拆分from查询的列
		String sqlFromB = "";
		// 拆分from查询条件
		String sqlFromE = "";
		// 组装SQL后的语句定义
		StringBuffer newSql = new StringBuffer();
		// 拼装WITH语句
		newSql.append(beginSql);
		// 拆分orderBY对应的列
		String orderSql = "";
		// 判断该SQL是否有误   将indexOf改成LastIndexOf
		if (StringUtils.isNotBlank(sql) && sql.indexOf(SQL_FROM) >= 0) {
			sqlFromB = sql.substring(0, sql.lastIndexOf(SQL_FROM));
			sqlFromE = sql.substring(sql.lastIndexOf(SQL_FROM), sql.length());
			if (sqlFromE.indexOf(SQL_ORDER_BY) >= 0) {
				sqlFromE = sqlFromE
						.substring(0, sqlFromE.lastIndexOf(SQL_ORDER_BY));
			} else {
				log.error("无ORDER BY子句");
			}
			// System.out.println(sqlFromE);
		} else {
			log.error("SQL语句有误");
		}
		newSql.append(sqlFromB);
		if (sql.indexOf(SQL_ORDER_BY) >= 0) {
			orderSql = sql.substring(sql.lastIndexOf(SQL_ORDER_BY), sql.length());
		} else {
			log.error("无ORDER BY 子句");
		}
		newSql.append(",ROW_NUMBER() over (" + orderSql + ") as " + TABLE_ID
				+ " ");
		newSql.append(sqlFromE);
		newSql.append(endSql);
		return newSql.toString();
	}

	/**
	 * 组装SQL语句总记录
	 * 
	 * @param sql
	 * @return
	 */
	public String getTotalCountSql(String sql) {
		StringBuffer sb = new StringBuffer();
		// SQL WITH语句定义
		String beginSql = SQL_WITH + " " + TABLE_ALIAS + " " + SQL_AS + " "
				+ SQL_BRACKET_RIGTH;
		sb.append(beginSql);
		sb.append(sql);
		sb.append(SQL_BRACKET_LEFT);
		sb.append(SQL_SELECT_COUNT_FORM);
		sb.append(TABLE_ALIAS);
		return sb.toString();
	}

	public static void main(String[] args) {
//		 String sql="select"+
//		 "q.vcQuoteNum,q.projectName,qf.vcModelNum,qf.vcPrice,qf.vcDiscount,qf.vcOldPrice,qf.vcQuantity,qf.vcPriceUnit"+
//		 "from tbQuoteFabric qf,tbQuote q where qf.nmQuoteId=q.nmId order by"+
//		 "qf.nmQuoteId";
		StringBuffer sb = new StringBuffer();
		sb.append("select a.TABLE_NAME \"TABLE_NAME\",a.TABLESPACE_NAME \"TABLESPACE_NAME\", ");
		sb.append("(select count(0) from user_tab_cols where table_name = a.table_name) \"COUNT\", ");
		sb.append("trunc(c.BYTES/1024/1024, 2) \"TABLE_SIZE\", b.COMMENTS \"TABLE_COMMENTS\" ");
		sb.append(" from user_tables a, user_tab_comments b, dba_segments c where a.TABLE_NAME = b.TABLE_NAME");
		sb.append(" and (c.owner = 'QBPT' or c.owner is null) and a.TABLE_NAME = c.segment_name(+) ");
		sb.append("and a.TABLE_NAME like '%%' order by a.TABLE_NAME");
//		String sql = "select vcQuoteLocal,vcQuoteNum,projectName,customer,projectDesignComp,vcSalesman,sumMoney from tbQuote q order by vcQuoteNum";
		SQLPageUtils pu = SQLPageUtils.getInstance();
		String newSql = pu.getAssemblyAfterSql(sb.toString(), 1, 2);
		 System.out.println(newSql);
		String totalSql = pu.getTotalCountSql(sb.toString());
		System.out.println(totalSql);
	}
}
